
# encoding=utf-8
from email import header
from urllib import response
import requests #导入requests模块
import json
import pprint
import time
import sys
from openpyxl import load_workbook, Workbook
import openpyxl


g_cookie = '''BIGipServer~Control_Center~POOL_POD9_PGW=3661373962.36895.0000; JSESSIONID=68EE9193D857F888DBDB5BED77C87EDC; jsSessionCookie="{+U5ryC/Od5iFdIvIhZbUZg==}zoycjl+Lr2zjNv/LtzwTtR2WtHsQd6JfG2lB1O9x2WhQAPLhYi5QKLTL+z4ANvsMPLvDhCZUijxs3g9eYAx/gQ3GIPG7gyzBYkqw1lGIIig="; jsAuthCookie-477196155="{EHuQCy0Zhji6gLFaaRKobA==}l98TPDTFtGQ1oN3lgNQk09nXCnQRQWitjzQcZPgPR6mWX0+5NvukpG/2IQ5gTP8n"; amplitude_id_0c88b9a41bdb4998ba859d566965a86410646.cn=eyJkZXZpY2VJZCI6IjNkMTBhY2Y1LTM2Y2EtNGY3NC05OGQ3LTNiZTI3MjNjMzVkM1IiLCJ1c2VySWQiOiI0NTk2MzE3Iiwib3B0T3V0IjpmYWxzZSwic2Vzc2lvbklkIjoxNjQ4NjA3NTMxNTc1LCJsYXN0RXZlbnRUaW1lIjoxNjQ4NjA3NTYxMDI0LCJldmVudElkIjozLCJpZGVudGlmeUlkIjoxOCwic2VxdWVuY2VOdW1iZXIiOjIxfQ=='''
g_token = ""

# 根据设备号获取simid
def get_simid_by_devid(devid_str):
    t = time.time()
    now_ms = int(round(t * 1000))
    now_ms_str = str(now_ms)
    page = "1"
    limit = "50"
    sort = "dateAdded"
    dir = "DESC"
    search = [{"property":"oneBox","type":"CONTAINS","value":devid_str,"id":"oneBox"}]
    search_str = json.dumps(search)
    # url = "https://cc1.10646.cn/provision/api/v1/sims?_dc={NowMs}&page=1&limit=50&sort=dateAdded&dir=DESC&search=%5B%7B%22property%22%3A%22oneBox%22%2C%22type%22%3A%22CONTAINS%22%2C%22value%22%3A%22LVSHPCHA1MS222640%22%2C%22id%22%3A%22oneBox%22%7D%5D"
    url = "https://cc1.10646.cn/provision/api/v1/sims?_dc={NowMs}&page=1&limit=50&sort=dateAdded&dir=DESC&search={Search}".format(NowMs = now_ms_str, Search = search_str)
    # print(url)
    # return
    header = {}
    header["Cookie"] = g_cookie
    header["_dc"] = now_ms_str
    header["limit"] = limit
    header["sort"] = sort
    header["dir"] = dir
    header["search"] = search_str
    response = requests.get(url=url, headers=header)
    json_data = response.json()
    # pprint.pprint(response.json())
    sim_id = 0
    data_vec = json_data["data"]
    if 0 == len(data_vec):
        return sim_id
    else:
        data = data_vec[0]
        if "simId" in data.keys():
            sim_id = int(data["simId"])
    # print(sim_id)
    return sim_id


# 刷新token
def refresh_token():
    url = "https://cc1.10646.cn//provision/api/v1/csrfToken/refresh"
    header = {}
    header["Cookie"] = g_cookie
    response = requests.get(url=url, headers=header)
    # print(response.text)
    global g_token
    g_token = response.text


# 检查导出密码
def check_export_passwd():
    url = "https://cc1.10646.cn/provision/api/v1/dataTrafficDetails/export/validatePwd"
    headers = {}
    # headers["Content-Type"] = "application/text"
    headers["Cookie"] = g_cookie
    headers["X-CSRF-TOKEN"] = g_token
    passwd = "Password!1234"
    response = requests.post(url=url, data=passwd, headers=headers)
    json_data = response.json()
    # pprint.pprint(json_data)


# 导出simid相关信息
def export_sim_info(dev_id, sim_id):
    t = time.time()
    now_ms = int(round(t * 1000))
    now_ms_str = str(now_ms)
    url = "https://cc1.10646.cn/provision/api/v1/dataTrafficDetails/export?_csrf={token}&_dc={NowMs}".format(token = g_token, NowMs = now_ms_str)

    header = {}
    header["Cookie"] = g_cookie
    header["Content-Type"] = "multipart/form-data; boundary=----WebKitFormBoundaryXAGnXK3M4uqeTwgM"

    params = {}
    export_columns = [{"header":"ICCID","dataIndex":"iccid","align":"left"},{"header":"VIN","dataIndex":"deviceId","align":"left"},{"header":"Session Start","dataIndex":"recordOpenTime","align":"left","exportRendererName":"dateTime"},{"header":"Data Volume (KB)","dataIndex":"roundedUsageKB","align":"right"},{"header":"Duration (Minutes)","dataIndex":"duration","align":"right","exportRendererName":"minuteDurationNA"},{"header":"Session Status","dataIndex":"sessionStatus","align":"left","exportRendererName":"sessionStatusRenderer"},{"header":"APN/DNN","dataIndex":"apn","align":"left"},{"header":"Rating Zone Name","dataIndex":"zoneName","align":"left"}]
    export_search_params = [{"name":"simId","value":sim_id,"type":"LONG_EQUALS"},{"property":"billingCycle.cycleStartDate","value":"03/01/2022","type":"BILLDATE_EQUALS"}]
    params["jasperExportSortDir"] = "DESC"
    params["jasperExportSortField"] = "recordOpenTime"
    params["jasperExportColumns"] = json.dumps(export_columns)
    params["jasperExportSearchParams"] = json.dumps(export_search_params)
    params["jasperExportResultCount"] = 1
    params["jasperExportLimitKey"] = "/provision/ui/terminals/sims/simDetails.html"

    response = requests.post(url=url, headers=header, params=params)
    # json_rsp = response.

    file_name = str(dev_id) + "_" + str(sim_id) + ".xlsx"
    # pprint.pprint(response.headers["Content-Disposition"])
    with open(file_name, "wb") as f:
        f.write(response.content)

def get_line(sheet, line_index):
    num_rows = sheet.max_row
    num_cols = sheet.max_column
    line = []
    for c in range(1, num_cols + 1):
        v = sheet.cell(line_index, c).value
        line.append(v)
    return line

# 取所有vin
def fetch_vin_list_from_excel():
    excel_path = "./0330VIN.xlsx"
    wb = load_workbook(excel_path)
    origin_sheet = wb["Sheet1"]
    num_rows = origin_sheet.max_row
    num_cols = origin_sheet.max_column

    vin_vec = []
    for r in range(2, num_rows + 1):
        vin = origin_sheet.cell(r, 1).value
        vin_vec.append(vin)
    
    return vin_vec


def handle():
    vin_vector = fetch_vin_list_from_excel()

    dev_id = ""
    sim_id = 0

    size = len(vin_vector)

    count = 0
    for vin in vin_vector:
        count += 1
        dev_id = vin
        promote_start = "共" + str(size) + "个 " + "正在进行第" + str(count) + "个  . " + dev_id + " 正在下载中..."
        print(promote_start)
        sim_id = get_simid_by_devid(dev_id)
        refresh_token()
        check_export_passwd()
        export_sim_info(dev_id, sim_id)
        promote_end = "共" + str(size) + "个 " + "正在进行第" + str(count) + "个  . " + dev_id + " 下载完成"
        print(promote_end)

if __name__=="__main__":
    handle()